import psycopg2
import mysql.connector  # 或者 import PyMySQL  
from mysql.connector import Error  
import requests


def fetch_mysql_file_urls(page_size, page_num):
    offset = (page_num - 1) * page_size
    file_urls = []
    warnId = []
    tableName = "risk_hidden_danger_warning_temp_24_08_31"

    # 连接到MySQL数据库  
    try:  
        connection = mysql.connector.connect(
            host='110.188.24.186',
            port=13306,
            database='hyzh-server',
            user='root',
            password='HYzh248713@.',
            connect_timeout=10  # 将10替换为所需的秒数
        )
        print("连接成功")
        if connection.is_connected():  
            print('Connected to MySQL database 33333')  
    
            cursor = connection.cursor()
            # 执行 SQL 查询
            sql_query = f"""
                    SELECT file_url,id
                    FROM {tableName}
                    where distort = 1
                    ORDER BY id
                    LIMIT {offset}, {page_size};
                    """
            cursor.execute(sql_query)
            # 获取所有查询结果
            results = cursor.fetchall()
            if results:
                # 如果有数据，添加到 file_urls 列表
                for row in results:
                    file_urls.append(row[0])
                    warnId.append(row[1])
        else:  
            print('No devices found in t_gate_device')  
    
        if warnId:
            # 构建 IN 子句
            in_clause = ', '.join([f"'{id}'" for id in warnId])
            
            # 执行删除语句
            delete_query = f"DELETE FROM {tableName} WHERE id IN ({in_clause})"
            cursor.execute(delete_query)
            connection.commit()
            print("执行删除语句成功")
        else:
            print("warnId 为空，不执行删除语句")
        
        return file_urls
    except Error as e:  
        print(f"Error connecting to MySQL222: {e}")  
    finally:  
        cursor.close()  
        connection.close()  
        print('MySQL connection is closed1111')
        

 #pg刪除文件
# 数据库查询函数，分页获取 file_url
def fetch_file_urls(page_size, page_num):
    offset = (page_num - 1) * page_size
    file_urls = []
    warnId = []
    tableName = "alarm_data_company_bak20240906"
    
    try:
        # 连接 PostgreSQL 数据库
        connection = psycopg2.connect(
            user="postgres",
            password="HYzh248713",
            host="110.188.24.250",
            port="25432",
            database="postgres"
        )
        
        cursor = connection.cursor()
        
        # 分页查询 SQL
        query = f"""
        SELECT file_url,id
        FROM {tableName}
        ORDER BY id
        LIMIT {page_size} OFFSET {offset};
        """
        
        cursor.execute(query)
        
        # 获取查询结果
        rows = cursor.fetchall()
        
        # 如果有数据，添加到 file_urls 列表
        for row in rows:
            file_urls.append(row[0])
            warnId.append(row[1])
        

        if warnId:
            # 构建 IN 子句
            in_clause = ', '.join([f"'{id}'" for id in warnId])
            
            # 执行删除语句
            delete_query = f"DELETE FROM {tableName} WHERE id IN ({in_clause})"
            cursor.execute(delete_query)
            connection.commit()
            print("执行删除语句成功")
        else:
            print("warnId 为空，不执行删除语句")



    except Exception as error:
        print(f"连接到 PostgreSQL 数据库时出错: {error}")
    
    finally:
        if connection:
            cursor.close()
            connection.close()
            print("PostgreSQL 连接已关闭")
    
    return file_urls

# API 请求函数，发送删除请求
def delete_files(file_urls):
    url = "https://msgxapi.scshyzh.cn:8743/alarmData-v8/filesDelete"
    # url = "http://10.3.18.234:8301/alarmData-v8/filesDelete"
    
    # 构建请求数据
    headers = {
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/123.0.0.0 Safari/537.36 Edg/123.0.0.0",
        "Content-Type": "application/json"
    }

   
    try:
        # 发送 POST 请求
        response = requests.post(url, headers=headers, json=file_urls)
        
        # 检查请求是否成功
        if response.status_code == 200:
            print("文件删除请求成功")
            print(response.json())  # 打印响应内容
        else:
            print(f"请求失败，状态码: {response.status_code}")
            print(response.text)
    
    except Exception as error:
        print(f"请求接口时出错: {error}")

# 主函数，分页处理数据并调用删除接口
def process_data_in_batches(page_size):
    page_num = 1
    
    while True:
    # 获取当前分页的 file_url
        file_urls = fetch_file_urls(page_size, page_num)
        # file_urls =  fetch_mysql_file_urls(page_size, page_num)
        print(file_urls)
        # 如果没有更多数据，则退出循环
        if not file_urls:
            print(f"所有分页数据已处理完毕，退出循环。")
            break
        
        # 调用 API 删除文件
        delete_files(file_urls)
        
        # 增加页码，处理下一页数据
        page_num += 1

# 设置分页大小并开始处理数据
page_size = 100
process_data_in_batches(page_size)
